package JDBC;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class Testtt {
    @Test  //插入
    public void  insert() throws Exception {
        Properties properties=new Properties();
        InputStream ips = Testtt.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(ips);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql="insert into table_name values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1, 10);
        preparedStatement.setObject(2, "s");
        preparedStatement.setObject(3, 10);

        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("插入成功");
        } else {
            System.out.println("插入失败");
        }
        preparedStatement.close();
        connection.close();


    }

    @Test  //删除
    public void  delect() throws Exception {
        Properties properties=new Properties();
        InputStream ips = Testtt.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(ips);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql="delete from table_name where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1, 9);


        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
        preparedStatement.close();
        connection.close();


    }

    @Test  //修改
    public void  update() throws Exception {
        Properties properties=new Properties();
        InputStream ips = Testtt.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(ips);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql = "update table_name set name=? where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setObject(1, "xxxxx");
        preparedStatement.setObject(2, "8");


        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }
        preparedStatement.close();
        connection.close();
    }

    @Test  //查询
    public void  select() throws Exception {
        Properties properties=new Properties();
        InputStream ips = Testtt.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(ips);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql = "select * from table_name where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, "8");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            System.out.println(id + "  " + name + "  " + age);
        }
        preparedStatement.close();
        connection.close();
    }

    @Test  //查询所有
    public void  select1() throws Exception {
        Properties properties=new Properties();
        InputStream ips = Testtt.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(ips);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        String sql = "select * from table_name";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            System.out.println(id + "  " + name + "  " + age);
        }
        preparedStatement.close();
        connection.close();
    }

}
